<?php

namespace Home\Model;

use Think\Model\RelationModel;

class ProjectModel extends RelationModel{

    protected $tablePrefix = 'p_';

    function getIndexProjects() {//获取项目首页所有项目列表
        $s_arr = session('user');
        $s_userid = $s_arr['id'];
        $m = M("p_user_identity");
        $projects = $m->query("SELECT I.project_id,I.isPinned,P.name,P.announcement,P.pStage,P.stage,P.timeNode FROM p_user_identity I LEFT JOIN p_project P on I.project_id = P.id "
                . "WHERE I.account_id= '" . $s_userid . "' AND I.isDeleted = 0 AND P.isDeleted = 0 AND P.stage !=4 AND P.isOpen = 0 ORDER BY I.isPinned DESC"); //获取我的项目列表
        $allowPicTypes = C("picTypes"); //允许图片上传后缀名
        foreach ($projects as $k => $v) {
//            $projects[$k]['time'] = getProjectEndTime($v['timeNode']);
//            if ($projects[$k]['time'] >= 24) {
//                $projects[$k]['timeNode'] = ceil($projects[$k]['time'] / 24) . "天";
//            } else {
//                $projects[$k]['timeNode'] = ceil($projects[$k]['time']) . "小时";
//            }
            $users = M("p_user_identity")->field("account_id")->where("project_id = '" . $v['project_id'] . "' and isDeleted = 0")->order("identities DESC")->select();
            $userIds = '';
            foreach ($users as $v2) {
                $userIds .= $v2['account_id'] . ",";
            }
            $userIds = substr($userIds, 0, -1);
            $users = M("u_account")->field("id,name,avatar")->where("id in (" . $userIds . ")")->limit(4)->select(); //每个项目下成员列表
            $isLeaderOrAgent = false;
            foreach ($users as $k2 => $v2) {
               $info = M("p_user_identity")->field("identities")->where("account_id = ".$v2['id']." AND project_id = ".$v['project_id']."")->find();
               $users[$k2]['identities'] = $info['identities'];
               $users[$k2]['avatar'] = getSingleField($v2['avatar'], 'sys_attachment', 'fileName');
               $identities[] = $info['identities'];

               if ($v2['id'] == $s_userid) {
                   $isLeaderOrAgent = isAgent((int)$info['identities']) || isLeader((int)$info['identities']);
               }
            }
            array_multisort($identities,SORT_DESC,$users);
            
            $projects[$k]['users'] = $users;
            $projects[$k]['latest'] = $this->getTaskLatest($v['project_id'], $s_userid, 2, $isLeaderOrAgent);
        }
        
        return $projects;
    }

    /**
     *  获取最近项目成员
     *
     * @modify 周仕林 2014-7-22 简化SQL语句，使用join语句消除嵌套查询
     * @param int $aId
     * @return array
     */
    function getRecentMembers($aId) {//获取最新成员
        $users = null;
        $m = M("p_user_identity");
        $projects = $m->field("project_id")->where(array('account_id' => $aId, 'isDeleted' => 0))->select();
        $projectIds = '';
        foreach ($projects as $v) {
            $projectIds .= $v['project_id'] . ",";
        }
        if ($projectIds) {
            $projectIds = substr($projectIds, 0, -1);
            $users = $m->alias('a')->join('join u_account b on b.id = a.account_id')
                    ->join('left join sys_attachment c on c.id = b.avatar')
                    ->Distinct(true)
                    ->field('a.account_id, b.name, b.email, c.fileName as avatar')
                    ->where(array(
                        'a.project_id' => array('in', $projectIds),
                        'b.id' => array('neq', $aId)
                    ))
                    ->order('a.createTime DESC')
                    ->limit(6)
                    ->select();
        }

        return $users;
    }
    
    /**
     * 通过项目Id和成员Id获取项目任务信息
     * 
     * @param int $pId
     * @param int $aId
     * @param string $time
     * @return array
     */
    function getPackagesAndTasksByProjectId($pId, $aId = null, $time = null) {
        $today = date("Y-m-d 00:00:00");
        $week = date('Y-m-d', (time() - ((date('w') == 0 ? 7 : date('w')) - 1) * 24 * 3600));
        $month = date('Y-m-d', strtotime(date('Y-m', time()) . '-01 00:00:00'));

        $where = array();
        if (!is_null($time)) {
            if ($time == 1) {
                $where['a.updateTime'] = array('gt', $today);
            } else if ($time == 7) {
                $where['a.updateTime'] = array('gt', $week);
            } else if ($time == 30) {
                $where['a.updateTime'] = array('gt', $month);
            } else if ($time == -30) {
                $where['a.updateTime'] = array('lt', $month);
            }
        }
        
        //添加任务归属条件
        if ($aId != -1) {
            if ($aId == 0) {
                $where['a.assignedTo'] = 0;
            } else {
                $where['a.assignedTo'] = $aId;
            }

            $packages = (array)M("p_task_package")->alias('a')->join('p_task b on b.package_id = a.id')
                    ->where(array(
                        'a.project_id' => $pId,
                        'a.isDeleted' => 0,
                        'b.isDeleted' => 0,
                        'b.assignedTo' => $aId
                    ))
                    ->order("a.id DESC")
                    ->field("a.id, a.name")
                    ->select();
        } else {
            $packages = M("p_task_package")->where(array('project_id' => $pId, 'isDeleted' => 0))->order("id DESC")->field("id, name")->select();
        }
        
        $tasks = (array)M('p_task')->alias('a')
                ->join('left join p_task_version b on b.task_id = a.id')
                ->join('left join sys_attachment c on c.id = b.attachment_id')
                ->join('left join u_account d on d.id = a.account_id')
                ->join('left join u_account e on e.id = a.assignedTo')
                ->join('left join u_account f on f.id = b.account_id')
                ->where($where)
                ->where(array(
                    'a.isDeleted' => 0,
                    'a.latestVersion = b.version or a.latestVersion = 0',
                    'a.project_id' => $pId,
                ))
                ->field('a.id as tId, a.name as tName, a.account_id as aId, a.isPinned, a.createTime, a.updateTime, a.assignedTo, a.latestVersion, a.package_id, c.id as attachment_id, c.fileName as fileUrl, c.fileType,'
                        . 'd.id as creatorId, d.name as creatorName, e.id as assignedToId, e.name as assignedToName, f.id as submitterId, f.name as submitterName, (select count(g.id) from p_task_discussion g where g.isDeleted = 0 and g.task_id = a.id) as disCount')
                ->order('a.id ASC')
                ->select();
        //循环检索任务
        foreach ($packages as $k => $package) {
            $packages[$k]['tasks'] = array();
            foreach ($tasks as $subk => $task) {
                if ($task['package_id'] == $package['id']) {
                    array_push($packages[$k]['tasks'], $task);
                    unset($tasks[$subk]);
                }
            }
        }
        
        return array('packages' => $packages, 'tasks' => $tasks);
    }
    /**
     * 最新提交任务
     * 
     * @param int $pId
     * @param int $limit
     * @param int $isLeaderOrAgent
     * @return array
     */
    function getTaskLatest($pId, $aId, $limit, $isLeaderOrAgent) {
        if ($isLeaderOrAgent) {
            $taskLatest = M('p_task')->alias('a')
                                ->join('p_task_version b on b.task_id = a.id')
                                ->join('sys_attachment c on c.id = b.attachment_id')
                                ->join('u_account d on d.id = b.account_id')
                                ->where(array(
                                    'a.project_id' => $pId,
                                    'a.isDeleted' => 0,
                                    //'a.status' => C('TASK_STATUS.NEWEST'),
                                    'a.latestVersion = b.version',
                                ))
                                ->field('a.id as tId, a.name as tName, a.updateTime, c.fileType, c.fileName, d.id as aId, d.name as aName')
                                ->order("a.updateTime DESC, a.id DESC")
                                ->limit($limit)
                                ->select();
        } else {
            $taskLatest = M('p_task')->alias('a')
                                ->join('p_task_version b on b.task_id = a.id')
                                ->join('sys_attachment c on c.id = b.attachment_id')
                                ->join('u_account d on d.id = b.account_id')
                                ->where(array(
                                    'a.project_id' => $pId,
                                    'a.isDeleted' => 0,
                                    'a.account_id' => $aId,
                                    //'a.status = ' . C('TASK_STATUS.NEWEST') . ' OR a.status = ' . C('TASK_STATUS.RETURNED'),
                                    'a.latestVersion = b.version',
                                ))
                                ->field('a.id as tId, a.name as tName, a.updateTime, c.fileType, c.fileName, d.id as aId, d.name as aName')
                                ->order("a.updateTime DESC, a.id DESC")
                                ->limit($limit)
                                ->select();
        }

        return $taskLatest;
    }

    /**
     * 通过项目Id和项目权限获取最新任务列表
     *
     * @param int $pId
     * @param int $isLeaderOrAgent
     * @return array
     */
    function getLastTasksByProjectId($pId, $aId, $isLeaderOrAgent) {
        //判断是否具有管理权限
        if ($isLeaderOrAgent) {
            $tasks = M('p_task')->alias('a')
                            ->join('left join p_task_version b on b.task_id = a.id')
                            ->join('left join sys_attachment c on c.id = b.attachment_id')
                            ->where(array(
                                'a.project_id' => $pId,
                                'a.isDeleted' => 0,
                                'a.latestVersion = b.version or a.latestVersion = 0',
                            ))
                            ->field('a.id as tId, a.name as tName, c.fileName as fileUrl, c.fileType')
                            ->order('a.updateTime DESC')
                            ->select();
        } else {
            $tasks = M('p_task')->alias('a')
                            ->join('left join p_task_version b on b.task_id = a.id')
                            ->join('left join sys_attachment c on c.id = b.attachment_id')
                            ->where(array(
                                'a.project_id' => $pId,
                                'a.isDeleted' => 0,
                                'a.latestVersion = b.version or a.latestVersion = 0',
                                'a.assignedTo=0 or a.assignedTo=' . $aId,
                            ))
                            ->field('a.id as tId, a.name as tName, c.fileName as fileUrl, c.fileType')
                            ->order('a.updateTime DESC')
                            ->select();
        }

        foreach ($tasks as $key => $task) {
            $tasks[$key]['fileUrl'] = getTaskUrl($task['fileUrl']);
        }

        return $tasks;
    }
    
    /**
     * 通过项目Id获取资料文件夹和文件
     * 
     * @param int $pId
     * @return string
     */
    function getDatasByProjectId($pId, $time = '',$isopen = 0) {
        $today = date("Y-m-d 00:00:00");
        $week = date('Y-m-d', (time() - ((date('w') == 0 ? 7 : date('w')) - 1) * 24 * 3600));
        $month = date('Y-m-d', strtotime(date('Y-m', time()) . '-01 00:00:00'));
        
        $sql = '1=1';
        if ($time == 1) {
            $sql .= " AND createTime >= '" . $today . "'";
        } else if ($time == 7) {
            $sql .= " AND createTime >= '" . $week . "'";
        } else if ($time == 30) {
            $sql .= " AND createTime >= '" . $month . "'";
        } else if ($time == -30) {
            $sql .= " AND createTime < '" . $month . "'";
        } else {
            $sql .= " AND createTime <= '" . date("Y-m-d H:i:s") . "'";
        }
        $limit = '';
        if($isopen){
            $limit = 10;
        }
        
        $folders = (array)M('p_file_folder')->where(array('project_id' => $pId, 'isDeleted' => 0))->where($sql)->order('id DESC')->limit($limit)->field('id, name,account_id as aId')->select();
        $sql = '1=1';
        if ($time == 1) {
            $sql .= " AND a.createTime >= '" . $today . "'";
        } else if ($time == 7) {
            $sql .= " AND a.createTime >= '" . $week . "'";
        } else if ($time == 30) {
            $sql .= " AND a.createTime >= '" . $month . "'";
        } else if ($time == -30) {
            $sql .= " AND a.createTime < '" . $month . "'";
        } else {
            $sql .= " AND a.createTime <= '" . date("Y-m-d H:i:s") . "'";
        }
        
        $files = (array)M('p_file')->alias('a')
                    ->join('u_account b on a.account_id = b.id')
                    ->join('sys_attachment c on c.id = a.attachment_id')
                    ->join('left join u_project_type d on d.id = a.type_id')
                    ->where(array(
                        'a.project_id' => $pId,
                        'a.isDeleted' => 0,
                    ))
                    ->where($sql)
                    ->field('a.id, a.name, a.folder_id,a.createTime, b.id as aId, b.name as aName, c.id as attachment_id, c.fileType, c.fileName, d.id as typeId, d.typeName')
                    ->order('a.id DESC')
                    ->select();
        
             foreach ($folders as $key => $folder) {
                $folders[$key]['files'] = array();
                foreach ($files as $subkey => $file) {
                    if ($file['folder_id'] == $folder['id']) {
                        array_push($folders[$key]['files'], $file);
                        unset($files[$subkey]);
                    }
                }
            }
        $shareFolders = M('u_account_data_share')->alias('a')
                            ->join('u_account_data_folder b on a.u_account_data_folder_id = b.id')
                            ->join('u_account c on c.id = a.account_id')
                            ->where(array(
                                'a.isDeleted' => 0,
                                'b.isDeleted' => 0,
                                'a.project_id' => $pId,
                            ))
                            ->where($sql)
                            ->field('a.id,a.u_account_data_folder_id as folderid, b.name, c.id as aId, c.name as aName')
                            ->select();
        
        return array('shareFolders' => $shareFolders, 'folders' => $folders, 'files' => $files);
    }
    
    function getDataNoPack($id, $sql = '') {//任务列表 （无包）
        $allowPicTypes = C("picTypes");
        $m = M("p_file");
        $files = $m->field("id,name,account_id,createTime,attachment_id,type_id")->where("project_id = " . $id . " AND folder_id = 0 AND isDeleted = 0 " . $sql . "")->order("id DESC")->select();
//       echo $m->getlastsql();
        if ($files) {
            foreach ($files as $k => $v) {
                $fileInfo = getSingleField($v['attachment_id'], 'sys_attachment', 'fileName');
                $files[$k]['fileImage'] = $fileInfo;
            }
        }
        return $files;
    }

    function getDiscuss($pId, $num = '') {//项目讨论区
        $allowPicTypes = C("picTypes"); //允许图片上传后缀名
        $discusses = M("p_discussion")->alias('a')
                        ->join('u_account b on a.account_id = b.id')
                        ->join('left join sys_attachment c on c.id = b.avatar')
                        ->where(array(
                            'a.project_id' => $pId,
                            'a.isDeleted' => 0,
                        ))
                        ->limit($num)
                        ->order('a.id DESC')
                        ->field('a.id, a.body, a.createTime, a.attachment_ids, b.id as aId, b.name as aName, c.fileName as avatar')
                        ->select();//讨论列表
        
        if ($discusses) {
            foreach ($discusses as $k => $v) {
                //附件
                $files = array();
                if ($v['attachment_ids']) {
                    $attachments = M("sys_attachment")->field("id,fileName")->where("id in (" . $v['attachment_ids'] . ")")->select();
                    
                    foreach ($attachments as $k2 => $v2) {
                        $type = strtolower(substr(strrchr($v2['fileName'], '.'), 1)); //文件类型
                        if (in_array($type, explode(",", $allowPicTypes))) {
                            $files[$k2]['fileImage'] = $v2['fileName'];
                            $files[$k2]['is_img'] = 'img';
                        } else {
                            $files[$k2]['fileImage'] = __APP__ . "/Public/images/tmp/" . getFileSuffix($v2['fileName'], 0);
                            $files[$k2]['is_img'] = '';
                        }
                        $files[$k2]['id'] = $v2['id'];
                        $files[$k2]['name'] = $v2['fileName'];
                    }
                }
                $discusses[$k]['files'] = $files;
            }
        }
        
        return $discusses;
    }

    function getDiscussByDiscussId($pId, $discussId) {//项目讨论区
        $allowPicTypes = C("picTypes"); //允许图片上传后缀名
        $discusses = M("p_discussion")->alias('a')
            ->join('u_account b on a.account_id = b.id')
            ->join('left join sys_attachment c on c.id = b.avatar')
            ->where(array(
                'a.project_id' => $pId,
                'a.isDeleted' => 0,
                'a.id' => array('lt', $discussId)
            ))
            ->limit(10)
            ->order('a.id DESC')
            ->field('a.id, a.body, a.createTime, a.attachment_ids, b.id as aId, b.name as aName, c.fileName as avatar')
            ->select();//讨论列表

        if ($discusses) {
            foreach ($discusses as $k => $v) {
                //附件
                $files = array();
                if ($v['attachment_ids']) {
                    $attachments = M("sys_attachment")->field("id,fileName")->where("id in (" . $v['attachment_ids'] . ")")->select();

                    foreach ($attachments as $k2 => $v2) {
                        $type = strtolower(substr(strrchr($v2['fileName'], '.'), 1)); //文件类型
                        if (in_array($type, explode(",", $allowPicTypes))) {
                            $files[$k2]['fileImage'] = $v2['fileName'];
                            $files[$k2]['is_img'] = 'img';
                        } else {
                            $files[$k2]['fileImage'] = __APP__ . "/Public/images/tmp/" . getFileSuffix($v2['fileName'], 0);
                            $files[$k2]['is_img'] = '';
                        }
                        $files[$k2]['id'] = $v2['id'];
                        $files[$k2]['name'] = $v2['fileName'];
                    }
                }
                $discusses[$k]['files'] = $files;
            }
        }

        return $discusses;
    }

    function getDiscussNum($id, $sql) {//项目讨论区数量
        return M("p_discussion")->where("project_id = '" . $id . "' AND isDeleted = 0 " . $sql . "")->count();
    }

    function getTaskRecycles($id) {//回收站任务
        $tasks = M("p_task")->field("id,name,account_id,deleteTime")->where("project_id = '" . $id . "' AND isDeleted = 1")
                        ->order("deleteTime DESC")->select();
        $allowPicTypes = C("picTypes"); //允许图片上传后缀名
        foreach ($tasks as $k => $v) {
            $taskinfo = M("p_task")->field("currentVersion")->where("id = '" . $v['id'] . "'")->find();
            $info = M("p_task_version")->alias('a')->join('sys_attachment b on b.id = a.attachment_id')->field("a.name, b.fileName")->where("a.task_id = '" . $v['id'] . "' AND a.version = " . $taskinfo['currentVersion'] . "")->find();
            $tasks[$k]['fileName'] = $info['name'];
            $type = strtolower(substr(strrchr($info['fileName'], '.'), 1)); //文件类型
            if (in_array($type, explode(",", $allowPicTypes))) {
                $tasks[$k]['fileImage'] = getTaskUrl($info['fileName']);

                $tasks[$k]['is_pic'] = 1;
            } else {
                $tasks[$k]['fileImage'] = __APP__ . "/Public/images/tmp/" . getFileSuffix($info['name'], 0);
            }
        }
        return $tasks;
    }

    function project_discuss_select($id) {//讨论
        $allowPicTypes = C("picTypes");
        $m = M("p_file");
        $files = $m->field("id,name,account_id,createTime,file_info,type_id")->where("project_id = " . $id . " AND folder_id = 0 AND isDeleted = 0")->order("id DESC")->select();
        //    echo $m->getlastsql();
        foreach ($files as $k => $v) {
            $type = strtolower(substr(strrchr($v['file_info'], '.'), 1)); //类型
            if (in_array($type, explode(",", $allowPicTypes))) {
                $files[$k]['is_img'] = "img";
            }
            if ($v['file_info'] == '') {
                $files[$k]['is_img'] = "hide";
            }
            $files[$k]['file_info'] = $v['file_info'];
        }
        return $files;
    }

    /**
     * 通过项目Id获取名片组信息
     *
     * @param int $pId
     * @return array
     */
    function getGroupsByProjectId($pId) {//名片
        $s_arr = session('user');
        //名片组
        $groups = M("p_card_group")->field("groupname,id")->where(array('account_id'=>$s_arr['id'],'project_id'=>$pId, 'isDeleted'=>0))->order("sort ASC")->select();
        if ($groups) {
            $p_card_book = M("p_card_book");
            foreach ($groups as $k => $v) {
                $groups[$k]['sub'] = $p_card_book->field("id,name,email,mobilePhone,companyName,companyPosition,introduction")->where(array('inGroup'=>$v['id'],'isDeleted'=>0))->order()->select();
                $groups[$k]['num'] = count($groups[$k]['sub']);
            }
        }

        return $groups;
    }

    /**
     *  验证项目Id集合是否正确
     *
     * @param  integer  $accountId   指定成员Id
     * @param  array    $projectIds  项目Id集合
     * @return array
     */
    public function validateProjectIds($accountId, $projectIds) {
        $whereArray = array();
        if (count($projectIds) > 1) {
            $whereArray = array('a.id' => array('in', implode(',', $projectIds)));
        } else {
            $whereArray = array('a.id' => $projectIds[0]);
        }

        $results = $this->alias('a')->join('RIGHT JOIN p_user_identity b ON a.id = b.project_id ')
                ->where($whereArray)
                ->where(array(
                    'a.stage != 4',
                    'a.isDeleted' => 0,
                    'b.isDeleted' => 0,
                    'b.account_id' => $accountId,
                ))
                ->field('a.id as projectId')
                ->select();

        if (count($projectIds) == count($results)) {
            return array(
                'isValid' => true,
                'projectIds' => $results
            );
        } else {
            return array(
                'isValid' => false,
            );
        }
    }

    /**
     * 通过项目Id和成员Id获取该项目信息（即成员是否有权查看该项目信息）
     *
     * @param type $projectId
     * @param type $accountId
     * @return type
     */
    public function getByProjectIdAndAccountId($projectId, $accountId) {
        return $this->alias('a')
                        ->join('left join p_user_identity b on a.id = b.project_id')
                        ->where(array(
                            'a.isDeleted' => 0,
                            array('a.stage' => array('lt', 4)),
                            'a.id' => $projectId,
                            'b.account_id' => $accountId,
                            'b.isDeleted' => 0,
                        ))
                        ->field('a.id, b.identities')
                        ->select();
    }

}
